using System;
using System.Data;
using System.Collections;
using Company.Product.Entity;
using Company.Product.Entity.ConditionForm;

namespace Company.Product.DAL
{
	/// <summary>
	/// Summary description for KeHoachChiTieu.
	/// </summary>
	public class KeHoachChiTieuDao
	{
	   private string _SQLSelectByKey;
	   private string _SQLUpdate;
	   private string _SQLInsert;
	   private string _SQLDelete;
	   private string _SQLSelectAll;

        #region Properties

        public IDbConnection _dbConnection;

        public IDbConnection DbConnection
        {
            get { return _dbConnection; }
            set { _dbConnection = value; }
        }

        #endregion
        
        #region Constructor

        public KeHoachChiTieuDao()
        {
            _dbConnection = null;
		InitSqlQuery();
        }

        public KeHoachChiTieuDao(IDbConnection con)
        {
            _dbConnection = con;
		InitSqlQuery();
        }

        #endregion


        private void ToLowerQuery()
        {
            _SQLDelete = _SQLDelete.ToLower();
            _SQLUpdate = _SQLUpdate.ToLower();
            _SQLInsert = _SQLInsert.ToLower();
            _SQLSelectByKey = _SQLSelectByKey.ToLower();
        }

        private void ToUpperQuery()
        {
            _SQLDelete = _SQLDelete.ToLower();
            _SQLUpdate = _SQLUpdate.ToLower();
            _SQLInsert = _SQLInsert.ToLower();
            _SQLSelectByKey = _SQLSelectByKey.ToLower();
        }

        #region InitSqlQuery
	
	private void InitSqlQuery()
	{
            string query;

            query = @"SELECT IDChiTieu, 
			LoaiChiTieu, 
			ThoiGianThem, 
			ThoiGianCapNhat, 
			SoTien, 
			GhiChu FROM KeHoachChiTieu WHERE (IDChiTieu = @IDChiTieu)";
	    _SQLSelectByKey = query;

            query = @"UPDATE KeHoachChiTieu SET LoaiChiTieu = @LoaiChiTieu, 
			ThoiGianThem = @ThoiGianThem, 
			ThoiGianCapNhat = @ThoiGianCapNhat, 
			SoTien = @SoTien, 
			GhiChu = @GhiChu WHERE (IDChiTieu = @IDChiTieu)";
	    _SQLUpdate      = query;

            query = @"INSERT INTO KeHoachChiTieu (IDChiTieu, LoaiChiTieu, 
			ThoiGianThem, 
			ThoiGianCapNhat, 
			SoTien, 
			GhiChu) VALUES (@IDChiTieu, @LoaiChiTieu, 
			@ThoiGianThem, 
			@ThoiGianCapNhat, 
			@SoTien, 
			@GhiChu)";
	    _SQLInsert      = query;

            query = @"DELETE FROM KeHoachChiTieu WHERE (IDChiTieu = @IDChiTieu)";
	    _SQLDelete      = query;

            query = @"SELECT IDChiTieu, 
			LoaiChiTieu, 
			ThoiGianThem, 
			ThoiGianCapNhat, 
			SoTien, 
			GhiChu 
		FROM 
			KeHoachChiTieu";
	    _SQLSelectAll   = query;
	}

        #endregion

        #region Save & Delete

        private int Save(IDbTransaction trans, KeHoachChiTieu obj, bool isExisting)
        {
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }

            IDbCommand command;
            //IDbTransaction trans;

            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                //trans = _dbConnection.BeginTransaction();

                command = _dbConnection.CreateCommand();

                if (!isExisting) { command.CommandText = _SQLInsert + ";"; }
                else { command.CommandText = _SQLUpdate + ";"; }

         DalTools.addDbDataParameter(command, "IDChiTieu", obj.IDChiTieu, DbType.Int32);
         DalTools.addDbDataParameter(command, "LoaiChiTieu", obj.LoaiChiTieu, DbType.Int32);
         DalTools.addDbDataParameter(command, "ThoiGianThem", obj.ThoiGianThem, DbType.DateTime);
         DalTools.addDbDataParameter(command, "ThoiGianCapNhat", obj.ThoiGianCapNhat, DbType.DateTime);
         DalTools.addDbDataParameter(command, "SoTien", obj.SoTien, DbType.Int32);
         DalTools.addDbDataParameter(command, "GhiChu", obj.GhiChu, DbType.String);

		if (trans != null) { command.Transaction = trans; }

                int res = command.ExecuteNonQuery();
                if (res == 0) { throw new Exception("Zero rows were affected."); }
                return res;
            }
            catch (Exception ex) { throw ex; }
        }

        public int Delete(IDbTransaction trans, KeHoachChiTieu obj)
        {
            if (_dbConnection == null)
            {
                //throw new DalException("Connection has not been set.");
                throw new Exception("Connection has not been set.");
            }

            IDbCommand command;

            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }

                command = _dbConnection.CreateCommand();
                command.CommandText = _SQLDelete + ";";

                FillParamToCommand(command, obj);

		if (trans != null) { command.Transaction = trans; }
                int res = command.ExecuteNonQuery();
                if (res == 0) { throw new Exception("Zero rows were affected."); }
                return res;
            }
            catch (Exception ex) { throw ex; }
        }

        public int Insert(IDbTransaction trans, KeHoachChiTieu obj)
        {
            return Save(trans, obj, false);
        }
        public int Update(IDbTransaction trans, KeHoachChiTieu obj)
        {
            return Save(trans, obj, true);
        }

        #endregion

        #region Command Execute & Read Properties

        private KeHoachChiTieuList SelectListByCommand(IDbTransaction trans, IDbCommand command)
        {
            KeHoachChiTieuList list = new KeHoachChiTieuList();
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }
            try
            {	
		KeHoachChiTieu obj;
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                if (trans != null) { command.Transaction = trans; }

                using (IDataReader reader = command.ExecuteReader())
                {
                    	while (reader.Read()) 
			{ 
				obj = new KeHoachChiTieu();
				ReadProperties(obj, reader);
				list.Add(obj); 
			}
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }

        private void DeleteByCommand(IDbTransaction trans, IDbCommand command)
        {
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }
            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                if (trans != null) { command.Transaction = trans; }
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private void ExecuteCommand(IDbTransaction trans, IDbCommand command)
        {
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }

            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                if (trans != null) { command.Transaction = trans; }
                if (command.ExecuteNonQuery() == 0) { throw new Exception("Zero rows were affected."); }
            }
            catch (Exception ex) { throw ex; }
        }

      private void ReadProperties(KeHoachChiTieu obj, IDataReader reader)
      {
         try
         {
                if(!(reader["IDChiTieu"] is DBNull))
		{
			obj.IDChiTieu = (Int32)reader["IDChiTieu"];
		}
                if(!(reader["LoaiChiTieu"] is DBNull))
		{
			obj.LoaiChiTieu = (Int32)reader["LoaiChiTieu"];
		}
                if(!(reader["ThoiGianThem"] is DBNull))
		{
			obj.ThoiGianThem = (DateTime)reader["ThoiGianThem"];
		}
                if(!(reader["ThoiGianCapNhat"] is DBNull))
		{
			obj.ThoiGianCapNhat = (DateTime)reader["ThoiGianCapNhat"];
		}
                if(!(reader["SoTien"] is DBNull))
		{
			obj.SoTien = (Int32)reader["SoTien"];
		}
                if(!(reader["GhiChu"] is DBNull))
		{
			obj.GhiChu = (string)reader["GhiChu"];
		}
         }
         catch (Exception ex)
         {
            //throw new DalException("Failed to read properties from DataReader.", ex);
            //throw new DalException("Failed to read properties from DataReader.\r\n" + ex.Message);
		throw ex;
         }
      }

        #endregion

      public KeHoachChiTieu SelectByKey(Int32 IDChiTieu)
      {
		return SelectByKey(null, IDChiTieu);
      }
      
	public KeHoachChiTieu SelectByKey(IDbTransaction trans, Int32 IDChiTieu)
	{
		IDbCommand command  = _dbConnection.CreateCommand();
		command.CommandText = _SQLSelectByKey;
         DalTools.addDbDataParameter(command, "IDChiTieu", IDChiTieu, DbType.Int32);
		KeHoachChiTieuList list =  SelectListByCommand(trans, command);

		if (list.Count == 0)
		{
			//throw new Exception("No data was returned"); 
                	return null;
            	}
            	return list[0];
	}

	public KeHoachChiTieuList SelectAll()
	{
		return SelectAll(null);		
	}

	public KeHoachChiTieuList SelectAll(IDbTransaction trans)
	{
		IDbCommand command  = _dbConnection.CreateCommand();
		command.CommandText = _SQLSelectAll;
		KeHoachChiTieuList list =  SelectListByCommand(trans, command);
		return list;
	}

        public KeHoachChiTieuList SelectByCondition(KeHoachChiTieuConditionForm condt)
        {
		return SelectByCondition(null, condt);
        }

        public KeHoachChiTieuList SelectByCondition(IDbTransaction trans, KeHoachChiTieuConditionForm condt)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = "SELECT * FROM KeHoachChiTieu " + GetCondtionQuery(condt) + ";";
            FillParamToCommand(command, condt);
            KeHoachChiTieuList list = SelectListByCommand(trans, command);
            return list;
        }

        private void DeleteByCondition(KeHoachChiTieuConditionForm condt)
        {
		DeleteByCondition(null, condt);
        }
        private void DeleteByCondition(IDbTransaction trans, KeHoachChiTieuConditionForm condt)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = "DELETE FROM KeHoachChiTieu " + GetCondtionQuery(condt) + ";";
            FillParamToCommand(command, condt);
            DeleteByCommand(trans, command);
        }

        #region Make Condition Query & Fill Param from Condition to Command

        #region Hidden

	/*
        private string GetCondtionQuery(KeHoachChiTieuConditionForm obj)
        {
            if (obj == null) { return ""; }
            string s = "";
            if (obj.IsSetIDChiTieu)
            {
                s += " AND IDChiTieu = @IDChiTieu ";
            }
            if (obj.IsSetLoaiChiTieu)
            {
                s += " AND LoaiChiTieu = @LoaiChiTieu ";
            }
            if (obj.IsSetThoiGianThem)
            {
                s += " AND ThoiGianThem = @ThoiGianThem ";
            }
            if (obj.IsSetThoiGianCapNhat)
            {
                s += " AND ThoiGianCapNhat = @ThoiGianCapNhat ";
            }
            if (obj.IsSetSoTien)
            {
                s += " AND SoTien = @SoTien ";
            }
            if (obj.IsSetGhiChu)
            {
                s += " AND GhiChu = @GhiChu ";
            }

            if (s.Length > 0)
            {
                return " WHERE " + s.Substring(4);
            }
            return "";
        }
	*/

        #endregion

        private string GetCondtionQuery(KeHoachChiTieuConditionForm obj)
        {
		return obj.ConditionQuery;
        }

        private void FillParamToCommand(IDbCommand command, KeHoachChiTieuConditionForm obj)
        {
         DalTools.addDbDataParameter(command, "IDChiTieu", obj.IDChiTieu, DbType.Int32);
         DalTools.addDbDataParameter(command, "LoaiChiTieu", obj.LoaiChiTieu, DbType.Int32);
         DalTools.addDbDataParameter(command, "ThoiGianThem", obj.ThoiGianThem, DbType.DateTime);
         DalTools.addDbDataParameter(command, "ThoiGianCapNhat", obj.ThoiGianCapNhat, DbType.DateTime);
         DalTools.addDbDataParameter(command, "SoTien", obj.SoTien, DbType.Int32);
         DalTools.addDbDataParameter(command, "GhiChu", obj.GhiChu, DbType.String);
        }


        private void FillParamToCommand(IDbCommand command, KeHoachChiTieu obj)
        {
         DalTools.addDbDataParameter(command, "IDChiTieu", obj.IDChiTieu, DbType.Int32);
         DalTools.addDbDataParameter(command, "LoaiChiTieu", obj.LoaiChiTieu, DbType.Int32);
         DalTools.addDbDataParameter(command, "ThoiGianThem", obj.ThoiGianThem, DbType.DateTime);
         DalTools.addDbDataParameter(command, "ThoiGianCapNhat", obj.ThoiGianCapNhat, DbType.DateTime);
         DalTools.addDbDataParameter(command, "SoTien", obj.SoTien, DbType.Int32);
         DalTools.addDbDataParameter(command, "GhiChu", obj.GhiChu, DbType.String);
        }
        
        public KeHoachChiTieuList SelectByTime(DateTime from, DateTime to)
		{
            string query;

            query = @"SELECT IDChiTieu, 
			LoaiChiTieu, 
			ThoiGianThem, 
			ThoiGianCapNhat, 
			SoTien, 
			GhiChu FROM KeHoachChiTieu WHERE (ThoiGianThem >= @from) AND (ThoiGianThem <= @to);";
			
			IDbCommand command  = _dbConnection.CreateCommand();
		command.CommandText = query;
         DalTools.addDbDataParameter(command, "from",from , DbType.DateTime);
         DalTools.addDbDataParameter(command, "to", to, DbType.DateTime);
         IDbTransaction trans = null;
		KeHoachChiTieuList list =  SelectListByCommand(trans, command);

		if (list.Count == 0)
		{
			//throw new Exception("No data was returned"); 
                	return null;
            }
            	return list;
	    }

        public KeHoachChiTieuList SelectByTime(DateTime month)
        {
            string query;

            query = @"SELECT IDChiTieu, 
			LoaiChiTieu, 
			ThoiGianThem, 
			ThoiGianCapNhat, 
			SoTien, 
			GhiChu FROM KeHoachChiTieu WHERE (ThoiGianThem >= @from) AND (ThoiGianThem <= @to);";

            DateTime from, to;
            from = month;
            to = month;
            //can fix lai
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = query;
            DalTools.addDbDataParameter(command, "from", from, DbType.DateTime);
            DalTools.addDbDataParameter(command, "to", to, DbType.DateTime);
            IDbTransaction trans = null;
            KeHoachChiTieuList list = SelectListByCommand(trans, command);

            if (list.Count == 0)
            {
                //throw new Exception("No data was returned"); 
                return null;
            }
            return list;
        }

        #endregion
   }
}